/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Factory.DAO.MySQL;

import DatabaseConnection.DBConnectionFactory;
import Factory.DAO.InventoryDAO;
import Factory.DAOFactory;
import Factory.Tables.Inventory;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Patrick
 */
public class MySQLInventoryDAO extends InventoryDAO{

    @Override
    public boolean isMovieQuantityAvailable(int productID, int quantity) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from inventory where productID=? and quantity>=?");
            pstmt.setInt(1, productID);
            pstmt.setInt(2, quantity);
            ResultSet rs = pstmt.executeQuery();
            Inventory inventory = null;
            while(rs.next()){
                inventory = new Inventory();
            }
            conn.close();
            if(inventory==null){
                return false;
            }
        }
        catch(Exception ex){
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    @Override
    public Inventory findInventoryByMovieID(int productID) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from inventory where idmovie=?");
            pstmt.setInt(1, productID);
            ResultSet rs = pstmt.executeQuery();
            Inventory inventory = null;
            while(rs.next()){
                inventory = new Inventory();
                inventory.setInventoryID(rs.getInt("idinventory"));
                inventory.setMovieID(rs.getInt("idmovie"));
                inventory.setQuantity(rs.getInt("quantity"));
                inventory.setDateOrder(rs.getDate("dateorder"));
                inventory.setTimeOrder(rs.getTime("timeorder"));
                }
            conn.close();
            return inventory;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public void updateInventoryQuantity(int movieID,int quantity) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update inventory SET quantity=quantity+? where idmovie=?");
            pstmt.setInt(1, quantity);
            pstmt.setInt(2, movieID);
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public int getQuantity(int movieID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select quantity from inventory where idmovie=?");
            pstmt.setInt(1, movieID);
            ResultSet rs = pstmt.executeQuery();
            int quantity = 0;
            while(rs.next()){
                quantity = rs.getInt("quantity");
            }
            conn.close();
            return quantity;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    @Override
    public void addInventory(Inventory obj) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("insert into inventory (quantity,dateorder,timeorder,idmovie) values (?,?,?,?)");
            pstmt.setInt(1, obj.getQuantity());
            pstmt.setDate(2, obj.getDateOrder());
            pstmt.setTime(3, obj.getTimeOrder());
            pstmt.setInt(4, obj.getMovieID());
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Inventory> getAllInventory() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from inventory natural join movie order by 'name'");
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Inventory> result = new ArrayList<>();
            Inventory inventory;
            while(rs.next()){
                inventory = new Inventory();
                inventory.setInventoryID(rs.getInt("idinventory"));
                inventory.setMovieID(rs.getInt("idmovie"));
                inventory.setMovieName(rs.getString("'name'"));
                inventory.setQuantity(rs.getInt("quantity"));
                inventory.setDateOrder(rs.getDate("dateorder"));
                inventory.setTimeOrder(rs.getTime("timeorder"));
                result.add(inventory);
            }
            conn.close();
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public void updateInventoryQuantityByInventoryID(int inventoryID, int quantity) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update inventory SET quantity=? where idinventory=?");
            pstmt.setInt(1, quantity);
            pstmt.setInt(2, inventoryID);
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void decreaseQuantity(int movieID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update inventory SET quantity=quantity-1 where idmovie=?");
            pstmt.setInt(1, movieID);
            
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    
    
    
    
    
}
